XplentyでFirestoreのAPIを叩いてデータを取得する

「カスタマーストーリー」を支えるツール特集

#REST

#ビジネス・アナリティクス

#ビッグデータ

#Xplenty

#Amazon Redshift

kobayashi.m

2020.07.06

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。今回もXplentyを使ってAPIを叩いてデータを取しそのデータをDBに保存してみます。

Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure

前回はレスポンスがそれほど複雑でないAPIとしてSlack Web APIを使ってチャンネル一覧を取得しました。今回は少しだけ複雑なレスポンスを返すAPIのデータをXplentyで取り扱ってみたいと思います。

題材は何にしようかと考えていたのですが、趣味で個人的にReactとFirebaseを使ってアプリを作っておりデータベースとしてFirestoreを使っていますが、Firestoreはデータを操作するAPIが用意されているのを思い出したのでここからデータを取得してRedshiftへ保存してみたいと思います。(FirestoreならBigQueryへエクスポートすればいいじゃんというのは置いておいて)

前回の記事

Firestoreの設定

FiresoteのAPIはCloud Firestore API | Firebase にドキュメントがあり詳しく書かれていますのでご確認ください。またFirestoreにXplentyから接続するためにルールを定義する必要がありますが、今回は簡単にReadはどこからでもアクセスできるようにしてあります。

以下が今回取得するFirestoreのドキュメントの中身になります。

単純なユーザー情報になりますが、一点hobbyがarray型になっておりこのままだとうまくRedshiftへデータを保存できないので、hobbyのみ別テーブルに切り出して保存したいと思います。

Firestore apiのレスポンス

{
  "name": "projects/fir-cm-kbm-test/databases/(default)/documents/sample-dataset/42iLM001obPwloaJ7tQC",
  "fields": {
    "age": {
      "integerValue": "20"
    },
    "mail": {
      "stringValue": "kenji@example.com"
    },
    "username": {
      "stringValue": "ケンジ"
    },
    "hobby": {
      "arrayValue": {
        "values": [
          {
            "stringValue": "読書"
          },
          {
            "stringValue": "登山"
          },
          {
            "stringValue": "ウォーキング"
          },
          {
            "stringValue": "渓流釣り"
          },
          {
            "stringValue": "ロードバイク"
          },
          {
            "stringValue": "BBQ"
          }
        ]
      }
    }
  },
  "createTime": "2020-06-25T02:30:28.534715Z",
  "updateTime": "2020-06-26T19:30:13.506135Z"
}

キー：データという形でなく型を挟むのでデータの取り扱いに注意します。また階層が深くなるのでXplentyの関数JsonStringToMap関数をネストして使います。

Xplentyのパッケージの作成

Xplentyのパッケージの中身は大まかに以下になります。

Firestore APIにリクエストを送りレスポンスを取得する
受け取ったデータを加工してRedshiftへ保存できる形に加工する
データをユーザーテーブル(fs_user)と趣味テーブル(fs_hobby)用に分ける
Redshiftへユーザーテーブル(fs_user)と趣味テーブル(fs_hobby)を作成しデータを登録する

これらのコンポーネントを組み合わせてパッケージを作成していきます。

はじめにサイドバーからPackagesを選択してNew Packageを押下し新しいパッケージを作成します。

1.Firestore API用にREST APIコンポーネントを作成

まずはFirestoreのAPIを使いドキュメントの情報を取得し、加工・保存を行います。

少しだけFirestoreのAPIの話になるのですが、Firestoreドキュメントを取得するので本来ならFirestore APIのDocumentを使えばいいのですが、XplentyのPaginationがFirestoreのPatinationに対応していないためDocumentだと全件取得できません。したがってrunQueryを使います。

手順1-1）.Add componetを押下し、SourcesでREST APIを選択する

手順1-2）.作成されたアイコンをダブルクリックするとREST APIコンポーネント設定用のモーダルが表示されるので、AuthenticationでNoneを選択する。

手順1-3）.Request and responseの設定を行いNextを押下する。

Method : POSTを選択
URL : https://firestore.googleapis.com/v1/projects/{プロジェクト名}/databases/(default)/documents:runQueryを入力
Headers
- Key : Content-Typeを入力
- VALUE : application/jsonを入力
Body :

{
        "structuredQuery":
            {
                "select": {
                    "fields":
                        [
                            {"fieldPath": "username"},
                            {"fieldPath": "mail"},
                            {"fieldPath": "age"},
                            {"fieldPath": "hobby"},
                        ]
                },
                "from": [
                    {"collectionId": "sample-dataset"}
                ]
            }
    }

Use Pagination : チェックしない

他の設定値はデフォルトのままにします。

手順1-4）.Response Schemaへ進み、Available Fieldsでfieldの右のプラスをクリックしSelected Fieldsでfieldが選択された状態にする。その後Saveを押下し、設定を保存する。

この画面ではレスポンスの内容を確認できるので必要なデータを確認しながら設定を行えます。

これでREST APIコンポーネントの設定は終わりますので、次にデータ加工用のコンポーネントをを設定します。

2.データ加工用のコンポーネント作成

Slack Web APIと同じ作りになります。Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。

手順2-1）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順2-2）.作成したSelectコンポーネントを前段のREST APIコンポーネントと接続する。（接続していないとカラムのマッピングができないため）

手順2-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION : Flatten(field)を入力
ALIAS : fieldを入力

手順2-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
JsonStringToMap(JsonStringToMap(field)#'document')#'name'	d_name
ToDate (JsonStringToMap(JsonStringToMap(field)#'document')#'createTime')	d_createTime
ToDate (JsonStringToMap(JsonStringToMap(field)#'document')#'updateTime')	d_updateTime
JsonStringToMap(JsonExtract(JsonStringToMap(field)#'document','$.fields.username'))#'stringValue'	username
JsonStringToMap(JsonExtract(JsonStringToMap(field)#'document','$.fields.mail'))#'stringValue'	mail
ParseIntOrDefault(JsonStringToMap(JsonExtract(JsonStringToMap(field)#'document','$.fields.age'))#'integerValue',null)	age
JsonStringToMap(JsonExtract(JsonStringToMap(field)#'document','$.fields.hobby'))#'arrayValue'	hobby

d_nameはユーザーテーブルのキーにあたります。

これでRedshiftへ保存する元データの加工は終わりになります。

3.データの分離

ここまでの加工ではユーザーデータの中に趣味テーブル用のデータがJson文字列として含まれてしまっている状態ですのでこれを分離してユーザーテーブル、趣味テーブル用にデータを作成します。

Xplentyではこの様に1つのデータから複数ディスティネーションを作りたい場合はCloneコンポーネントを使えば良いです。

手順3-1）.Add componetを押下し、TransformationsでCloneを選択してCloneコンポーネントを作成しSlectコンポーネントと接続する

ここでhobby用のデータはjson文字列のデータになっています。これは(手順2)を始める前とデータの状態と同じ様な状態なので(手順2)と同じ手順でデータを加工します。

手順3-2）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順3-3）.作成したSelectコンポーネントを前段のCloneと接続する

手順2-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
d_name	d_name
Flatten(JsonStringToBag(JsonStringToMap(hobby)#'values'))	hobby

ユーザーデータのキーに当たるものも渡してこれを条件にユーザーテーブルと趣味テーブルを結合したいので残しておきます。

手順2-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
d_name	d_name
JsonStringToMap(hobby)#'stringValue'	hobby

これでユーザーデータと趣味データが分離でき、Redshiftへ保存する形へデータを変換できましたので、最後にRedshiftへ保存する設定をします。

4.保存先としてRedshiftコンポーネント作成

Slack Web APIのときと同様に簡単に行うためテーブルは予め作成せずXplentyのジョブで自動作成し、データは毎回洗い替えを行う設定にします。

手順4-1）.Add componetを押下し、DestinationsでAmazon Redshiftを選択する

手順4-2）.画面上にRedshiftのアイコンが作成されるので先に作成したSelectコンポーネントのアイコンとDrag&Dropで紐付ける。

手順4-3）.RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。

手順4-4）.移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Nextを押下する。

Automatically create table if it doesn't exist : チェックする
Automatically add missing columns : チェックする
Operation type : Overwrite (Truncate and insert)を選択する

他の設定はデフォルトで問題ありません。

手順4-5）.移行元と移行先のカラムマッピングを行う。移行先のテーブルは新規作成なのでAuto fillを押下して自動設定を行う。

手順4-6）.右下のSaveを押下して設定を保存する。

手順4-7）.(手順4-1〜4-6)を繰り返して趣味テーブル(fs_hobby)の設定を行う

これでパッケージの作成は完了です。

Xplentyのパッケージの実行と確認

実際にジョブを実行して結果を確認します。

Redshitでデータを確認

ユーザーテーブル

select * from cm_kobayashi_test.fs_user;

d_name	d_createtime	d_updatetime	username	mail	age
rs_42iLM001obPwloaJ7tQC	2020-06-25 02:30:28.534000	2020-06-26 19:30:13.506000	ケンジ	kenji@example.com	20
rs_96e9QOLYdI9VAgaGh4nA	2020-06-25 02:32:14.491000	2020-06-25 04:02:59.448000	ハナコ	hanako@example.com	18
rs_EcsnAPIbAHEloM5ofn5s	2020-06-25 02:31:11.174000	2020-06-25 04:03:22.915000	タロウ	taro@example.com	19

Redshitでデータを確認

趣味テーブル

select * from cm_kobayashi_test.fs_hobby;

d_name	hobby
rs_42iLM001obPwloaJ7tQC	読書
rs_42iLM001obPwloaJ7tQC	登山
rs_42iLM001obPwloaJ7tQC	ウォーキング
rs_42iLM001obPwloaJ7tQC	渓流釣り
rs_42iLM001obPwloaJ7tQC	ロードゲーム
rs_42iLM001obPwloaJ7tQC	BBQ
rs_96e9QOLYdI9VAgaGh4nA	筋トレ
rs_96e9QOLYdI9VAgaGh4nA	ヨガ
rs_96e9QOLYdI9VAgaGh4nA	温泉巡り
rs_EcsnAPIbAHEloM5ofn5s	温泉巡り
rs_EcsnAPIbAHEloM5ofn5s	ロードゲーム